package tests;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class TestHSQLDB 
{
	public static void main(String[] args)
	{
		try 
		{
			Class.forName("org.hsqldb.jdbcDriver");
			
			Connection con = DriverManager.getConnection("jdbc:hsqldb:file:desansdishuitre", "sa", "");
			
			Statement st = con.createStatement();
			
			st.executeUpdate("CREATE TABLE monogram (id IDENTITY , mot VARCHAR(20) NOT NULL, proba FLOAT NOT NULL, repli FLOAT NOT NULL)");
			st.executeUpdate("CREATE TABLE bigram (id IDENTITY, mot1 VARCHAR(20), mot2 VARCHAR(20), proba FLOAT NOT NULL, repli FLOAT NOT NULL)");			
			st.executeUpdate("CREATE TABLE trigram (id IDENTITY, mot1 VARCHAR(20), mot2 VARCHAR(20), mot3 VARCHAR(20), proba FLOAT NOT NULL)");
			
////////////////////////////////////////////////////////////////////
			FileReader fr = new FileReader("data/ML_3-gram_JEP-2002-2004-2008.arpa") ;
			BufferedReader br = new BufferedReader(fr) ;
			
			String line;
			String [] ligneParsee = null ;
			while ((line = br.readLine()) != null)
			{
				if (line.equals("\\1-grams:"))
					break;
			}
			
			String requete = "INSERT INTO monogram (mot,proba,repli) VALUES (?, ?, ?)";
			PreparedStatement ps = con.prepareStatement(requete);
			while ((line = br.readLine()) != null)
			{
				if (line.equals("\\2-grams:"))
					break;
				else if (!line.trim().isEmpty())
				{
					ligneParsee = line.split("\\t") ;
					ps.setFloat(2, Float.parseFloat(ligneParsee[0])) ;
					ps.setString(1, ligneParsee[1]) ;
					ps.setFloat(3, Float.parseFloat(ligneParsee[2])) ;
					
					ps.executeUpdate();
				}
			}
			
			requete = "INSERT INTO bigram (proba, mot1, mot2, repli) VALUES (?, ?, ?, ?)";
			ps = con.prepareStatement(requete);
			while ((line = br.readLine()) != null)
			{
				if (line.equals("\\3-grams:"))
					break;
				else if (!line.trim().isEmpty())
				{
					ligneParsee = line.split("\\t") ;	
					ps.setString(2, ligneParsee[1]);
					ps.setString(3, ligneParsee[2]);
					
					ps.setFloat(1, Float.parseFloat(ligneParsee[0])) ;
					ps.setFloat(4, Float.parseFloat(ligneParsee[3])) ;
				
					ps.executeUpdate();
				}
			}
			
		
			br.close();
			
//////////////////////////////////////////////////////////////////////
			ResultSet rs = st.executeQuery("SELECT * FROM monogram");
			
			ResultSetMetaData meta   = rs.getMetaData();
	        int               colmax = meta.getColumnCount();
	        Object            o = null;

	        for (; rs.next(); ) {
	            for (int i = 0; i < colmax; ++i) {
	                o = rs.getObject(i + 1);   

	                System.out.print(o.toString() + " ");
	            }

	            System.out.println(" ");
	        }

	        rs = st.executeQuery("SELECT * FROM bigram");
			
			meta   = rs.getMetaData();
	        colmax = meta.getColumnCount();
	        o = null;

	        for (; rs.next(); ) {
	            for (int i = 0; i < colmax; ++i) {
	                o = rs.getObject(i + 1);   

	                System.out.print(o.toString() + " ");
	            }

	            System.out.println(" ");
	        }

			
			st.execute("SHUTDOWN");
		} 
		catch (Exception e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
